package com.njcky.service;import cn.hutool.core.bean.BeanUtil;import com.alibaba.fastjson.JSON;import com.alibaba.fastjson.JSONArray;import com.alibaba.fastjson.JSONObject;import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;import com.fasterxml.jackson.core.type.TypeReference;import com.fasterxml.jackson.databind.ObjectMapper;import com.google.common.collect.Sets;import com.njcky.config.FieldConfig;import com.njcky.model.NodePo;import com.njcky.model.SectionLine;import com.njcky.model.SectionPoint;import com.njcky.model.dto.*;import com.njcky.model.vo.CrossSectionAnalysisVo;import com.njcky.model.vo.SectionAnalysisVo;import com.njcky.utils.citylife.AesUtil;import com.njcky.utils.citylife.exception.CustomException;import com.njcky.utils.db.DBPool2DataSourceUtil;import com.njcky.utils.db.DBPoolDataSourceUtil;import com.njcky.utils.page.HttpStatus;import com.njcky.utils.page.entity.TableDataInfo;import com.yutu.map.service.model.GeoCompareModel;import lombok.extern.slf4j.Slf4j;import org.apache.commons.lang3.ObjectUtils;import org.apache.commons.lang3.StringUtils;import org.geotools.geometry.jts.JTSFactoryFinder;import org.locationtech.jts.geom.*;import org.locationtech.jts.io.ParseException;import org.locationtech.jts.io.WKTReader;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.beans.factory.annotation.Value;import org.springframework.stereotype.Service;import javax.annotation.PostConstruct;import javax.annotation.PreDestroy;import java.math.BigDecimal;import java.math.MathContext;import java.math.RoundingMode;import java.sql.SQLException;import java.sql.Timestamp;import java.time.LocalDate;import java.time.LocalDateTime;import java.time.format.DateTimeFormatter;import java.time.format.DateTimeParseException;import java.util.*;import java.util.concurrent.*;import java.util.function.Supplier;import java.util.logging.Logger;import java.util.stream.Collectors;@Slf4j@Servicepublic class ProjectPptimizationService {    private static final Logger logger = Logger.getLogger(ProjectPptimizationService.class.getName());    @Autowired    private DBPoolDataSourceUtil poolDataSourceUtil;    private static final ObjectMapper objectMapper = new ObjectMapper();    @Value("${map.status}")    private boolean status;    @Value("${map.srid}")    private Integer srid;    public static final int VAL = 110771;    // 表结构缓存（类级别缓存，避免重复查询information_schema）    private final Map<String, Map<String, Object>> tableColumnsCache = new ConcurrentHashMap<>();    private static final long CACHE_EXPIRE_MS = 3600 * 1000;    // 初始化定时清理过期缓存    @PostConstruct    public void initCacheCleaner() {        ScheduledExecutorService scheduler = Executors.newSingleThreadScheduledExecutor();        scheduler.scheduleAtFixedRate(() -> {            long now = System.currentTimeMillis();            Iterator<Map.Entry<String, Map<String, Object>>> iterator = tableColumnsCache.entrySet().iterator();            while (iterator.hasNext()) {                Map.Entry<String, Map<String, Object>> entry = iterator.next();                long timestamp = (long) entry.getValue().get("timestamp");                if (now - timestamp > CACHE_EXPIRE_MS) {                    iterator.remove();                }            }        }, 1, 1, TimeUnit.HOURS);    }    // 批量查询表ID    public List<Integer> tableParse(List<String> tables) {        if (CollectionUtils.isEmpty(tables)) {            return new ArrayList<>();        }        String tableNames = tables.stream()                .map(table -> "'" + table.replace("'", "''") + "'")                .collect(Collectors.joining(","));        String sql = "SELECT id FROM sys_result_tem WHERE note IN (" + tableNames + ")";        return poolDataSourceUtil.NpgNormal_GetListDic(sql).stream()                .map(map -> Integer.parseInt(map.get("id").toString()))                .collect(Collectors.toList());    }    // 批量处理IDs    private List<Integer> getIds(List<Integer> ids) {        if (CollectionUtils.isEmpty(ids)) {            return new ArrayList<>();        }        String idsStr = ids.stream().map(String::valueOf).collect(Collectors.joining(","));        String sql = "SELECT * FROM njssmx_sjgl.sys_result_tem WHERE id IN (" + idsStr + ")";        List<Map<String, Object>> resultMap = poolDataSourceUtil.NpgNormal_GetListDic(sql);        List<SysInParamPo> sysInParamPoList = resultMap.stream()                .map(map -> JSON.parseObject(JSON.toJSONString(map), SysInParamPo.class))                .collect(Collectors.toList());        if (CollectionUtils.isEmpty(sysInParamPoList)) {            return new ArrayList<>();        }        List<String> notes = sysInParamPoList.stream()                .map(SysInParamPo::getNote)                .distinct()                .filter(StringUtils::isNotEmpty)                .collect(Collectors.toList());        if (CollectionUtils.isEmpty(notes)) {            return new ArrayList<>();        }        String notesStr = notes.stream()                .map(note -> "'" + note.replace("'", "''") + "'")                .collect(Collectors.joining(","));        String sql1 = "SELECT id FROM njssmx_sjgl.sys_result_tem WHERE note IN (" + notesStr + ")";        List<Map<String, Object>> resultMap1 = poolDataSourceUtil.NpgNormal_GetListDic(sql1);        return resultMap1.stream()                .map(map -> Integer.parseInt(map.get("id").toString()))                .collect(Collectors.toList());    }    // 批量处理关联查询    private List<SysInParamDto> getParams(InParamDto inParamDto) {        List<Integer> ids = inParamDto.getIds();        if (CollectionUtils.isEmpty(ids)) {            return new ArrayList<>();        }        String idsStr = ids.stream().map(String::valueOf).collect(Collectors.joining(","));        String sql = "SELECT * FROM njssmx_sjgl.sys_node_relation WHERE node_id_point IN (" + idsStr + ")";        List<Map<String, Object>> resultMap = poolDataSourceUtil.NpgNormal_GetListDic(sql);        List<NodePo> list = resultMap.stream()                .map(map -> JSON.parseObject(JSON.toJSONString(map), NodePo.class))                .collect(Collectors.toList());        List<Integer> lineList = list.stream().map(NodePo::getNodeIdLine).collect(Collectors.toList());        List<Integer> roomList = list.stream().map(NodePo::getNodeIdRoom).collect(Collectors.toList());        ids.addAll(lineList);        ids.addAll(roomList);        List<Integer> sysIds = getIds(ids);        if (CollectionUtils.isEmpty(sysIds)) {            return new ArrayList<>();        }        String sysIdsStr = sysIds.stream().map(String::valueOf).collect(Collectors.joining(","));        String sql2 = "select id,value,parent_id as parentId,note,sort,is_show as isShow " +                "from njssmx_sjgl.sys_result_tem as a WHERE a.id IN (" + sysIdsStr + ")";        List<Map<String, Object>> resultMap2 = poolDataSourceUtil.NpgNormal_GetListDic(sql2);        return resultMap2.stream()                .map(map -> JSON.parseObject(JSON.toJSONString(map), SysInParamDto.class))                .collect(Collectors.toList());    }    /**     * 核心调用主函数(查询管线相关)     * TODO 接口优化     *     * @param inParamDto     * @return     */    public List<Map<String, Object>> attributeSearch(InParamDto inParamDto) {        List<Map<String, Object>> result = new ArrayList<>();        if (CollectionUtils.isEmpty(inParamDto.getTables())) {            return result;        }        // 预处理空间坐标（不变）        if (!StringUtils.isEmpty(inParamDto.getShape())) {            if (getCoordinate4549(inParamDto.getShape()) != null) {                inParamDto.setShape(getCoordinate4549(inParamDto.getShape()));            } else {                return null;            }        }        // 批量获取表ID（不变）        inParamDto.setIds(tableParse(inParamDto.getTables()));        if (CollectionUtils.isEmpty(inParamDto.getIds())) {            return result;        }        // 获取查询参数（不变）        List<SysInParamDto> paramList = getParams(inParamDto);        if (CollectionUtils.isEmpty(paramList)) {            return result;        }        // 参数预处理（不变）        String keyPrefix = "postgres.";        paramList.forEach(info -> {            if (!StringUtils.isEmpty(info.getNote())) {                info.setKey(keyPrefix + info.getNote());                info.setShape(inParamDto.getShape());                info.setBufferRange(inParamDto.getBufferRange() == null ? 0 : inParamDto.getBufferRange());                info.setIsPublic(inParamDto.getIsPublic());                info.setUpDown(inParamDto.getUpDown());                if (!StringUtils.isEmpty(inParamDto.getResult())) {                    info.setResult(AesUtil.decrypt(inParamDto.getResult()));                }            }        });        // 去重表名（不变）        List<SysInParamDto> uniqueParams = paramList.stream()                .collect(Collectors.collectingAndThen(                        Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(SysInParamDto::getNote))),                        ArrayList::new));        ThreadLocal<List<Object>> currentParamHolder = new ThreadLocal<>();        // 定义需要特殊处理的数字字段列表（不变）        List<String> numericFields = Arrays.asList(                "objectid", "qdgc", "zdgc", "qdms", "zdms", "zks", "zyks", "lxlx",                "syzt", "hls", "qybs", "dsdxbs", "source", "target", "length"                , "gdcd", "gdqddmgc", "gdzddmgc", "xzb", "yzb", "dmgc",                "jsdm", "xzj", "js", "jgc", "jgk", "jbs", "bhgd", "qybs", "dsdxbs",                "syzt", "jdgc"        );        // 循环查询各表数据        for (SysInParamDto param : uniqueParams) {            try {                currentParamHolder.set(new ArrayList<>());                List<Object> currentParams = currentParamHolder.get();                String tableKey = param.getKey();                if (StringUtils.isEmpty(tableKey)) {                    continue;                }                String[] schemaTable = tableKey.split("\\.");                if (schemaTable.length != 2) {                    continue;                }                // 构建SQL（基础部分不变）                StringBuilder sqlBuilder = new StringBuilder();                sqlBuilder.append("SELECT ")                        .append("a.*, ")                        .append("a.xzq as ssqy, ")                        .append("a.zydw as tcdw, ")                        .append(" COUNT ( * ) OVER ( ) AS ctest,")                        .append("st_astext( st_transform(sde.st_geometry(st_astext(a.shape)::TEXT,4528), 4490)) as shapetext ")                        .append("FROM ").append(tableKey).append(" as a WHERE 1=1 ");                // 空间过滤条件（不变）                if (StringUtils.isNotEmpty(param.getShape())) {                    sqlBuilder.append(" AND sde.st_intersects(a.shape, ")                            .append("sde.st_buffer(sde.st_geometry(?, sde.st_srid ( A.shape )), ?)) ");                    currentParams.add(param.getShape());                    currentParams.add(param.getBufferRange());                }                // 模糊查询条件（不变）                List<String> columns = getTableColumns(schemaTable[0], schemaTable[1]);                if (!StringUtils.isEmpty(inParamDto.getVagueQueryParam()) && !CollectionUtils.isEmpty(columns)) {                    sqlBuilder.append(" AND (");                    for (int i = 0; i < columns.size(); i++) {                        String column = columns.get(i);                        sqlBuilder.append("CAST(a.").append(column).append(" AS TEXT) LIKE CONCAT('%', ?, '%')");                        if (i != columns.size() - 1) {                            sqlBuilder.append(" OR ");                        }                        currentParams.add(inParamDto.getVagueQueryParam());                    }                    sqlBuilder.append(") ");                }                // 时间范围过滤（不变）                if (!StringUtils.isEmpty(inParamDto.getHistoryQueryDataTimeParam())                        && inParamDto.getStartQueryParamValue() != null                        && inParamDto.getEndQueryParamValue() != null) {                    String timeColumn = inParamDto.getHistoryQueryDataTimeParam();                    String start = inParamDto.getStartQueryParamValue();                    String end = inParamDto.getEndQueryParamValue();                    if ("jsnd".equals(timeColumn)) {                        try {                            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");                            LocalDate startDate = LocalDate.parse(start, formatter);                            LocalDate endDate = LocalDate.parse(end, formatter);                            int startYear = startDate.getYear();                            int endYear = endDate.getYear();                            if (startYear == endYear) {                                sqlBuilder.append(" AND a.").append(timeColumn).append(" = ? ");                                currentParams.add(String.valueOf(startYear));                            } else {                                sqlBuilder.append(" AND a.").append(timeColumn).append(" BETWEEN ? AND ? ");                                currentParams.add(String.valueOf(startYear));                                currentParams.add(String.valueOf(endYear));                            }                        } catch (DateTimeParseException e) {                            sqlBuilder.append(" AND a.").append(timeColumn).append(" BETWEEN ? AND ? ");                            currentParams.add(start);                            currentParams.add(end);                        }                    } else {                        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");                        try {                            LocalDate startDate = LocalDate.parse(start, formatter);                            LocalDate endDate = LocalDate.parse(end, formatter);                            LocalDateTime startDateTime = startDate.atStartOfDay();                            LocalDateTime endDateTime = endDate.atTime(23, 59, 59, 999_000_000);                            currentParams.add(Timestamp.valueOf(startDateTime));                            currentParams.add(Timestamp.valueOf(endDateTime));                            sqlBuilder.append(" AND a.").append(timeColumn).append(" BETWEEN ? AND ? ");                        } catch (DateTimeParseException e) {                            currentParams.add(start);                            currentParams.add(end);                            sqlBuilder.append(" AND a.").append(timeColumn).append(" BETWEEN ? AND ? ");                        }                    }                }                // -------------------------- 核心修改：区间匹配（只保留参数值到上限的范围） --------------------------                List<String> historyParams = inParamDto.getHistoryQueryParam();                List<Object> historyValues = inParamDto.getHistoryQueryParamValue();                if (!CollectionUtils.isEmpty(historyParams) && !CollectionUtils.isEmpty(historyValues)                        && historyParams.size() == historyValues.size()) {                    for (int i = 0; i < historyParams.size(); i++) {                        String paramName = historyParams.get(i);                        Object paramValue = historyValues.get(i);                        String columnType = getColumnType(schemaTable[0], schemaTable[1], paramName);                        // 步骤1：转换为BigDecimal确保精度                        BigDecimal num = null;                        if (numericFields.contains(paramName)) {                            try {                                if (paramValue instanceof String) {                                    num = new BigDecimal((String) paramValue);                                } else if (paramValue instanceof Number) {                                    num = new BigDecimal(paramValue.toString());                                }                            } catch (NumberFormatException e) {                                continue; // 转换失败则跳过                            }                        }                        // 步骤2：处理带引号字符串（不变）                        String strParamValue = paramValue instanceof String ? (String) paramValue : String.valueOf(paramValue);                        boolean isQuotedStrParam = strParamValue.startsWith("\"") && strParamValue.endsWith("\"");                        String actualStrValue = isQuotedStrParam ? strParamValue.substring(1, strParamValue.length() - 1) : strParamValue;                        // 场景1：带引号字符串 → 精确匹配                        if (isQuotedStrParam) {                            sqlBuilder.append(" AND a.").append(paramName).append(" = ? ");                            currentParams.add(actualStrValue);                            // 场景2：数字参数 → 区间匹配（核心修改）                        } else if (num != null && "numeric".equals(columnType)) {                            // 1. 计算有效小数位数（如4.4→1位，4.23→2位）                            BigDecimal strippedNum = num.stripTrailingZeros();                            int decimalPlaces = strippedNum.scale() > 0 ? strippedNum.scale() : 0;                            // 2. 动态步长（1位→0.1，2位→0.01）                            BigDecimal step = new BigDecimal("1")                                    .divide(new BigDecimal("10").pow(decimalPlaces),                                            decimalPlaces + 1,                                            RoundingMode.HALF_UP);                            // 3. 计算上限（4.4+0.1=4.5，4.23+0.01=4.24）                            BigDecimal upperBound = num.add(step);                            // 4. 核心：只保留 [参数值, 上限) 区间的数据                            sqlBuilder.append(" AND a.").append(paramName).append(" >= ? ")                                    .append(" AND a.").append(paramName).append(" < ? ");                            currentParams.add(num);          // 下限：4.4                            currentParams.add(upperBound);   // 上限：4.5                            // 其他场景保持不变                        } else if (num != null) {                            sqlBuilder.append(" AND a.").append(paramName).append(" IS NOT NULL ")                                    .append(" AND a.").append(paramName).append(" LIKE ? || '%' ")                                    .append(" AND LENGTH(a.").append(paramName).append(") = LENGTH(?) ");                            currentParams.add(actualStrValue);                            currentParams.add(actualStrValue);                        } else {                            sqlBuilder.append(" AND a.").append(paramName).append(" IS NOT NULL ")                                    .append(" AND CAST(a.").append(paramName).append(" AS TEXT) LIKE CONCAT('%', ?, '%') ");                            currentParams.add(actualStrValue);                        }                    }                }                // 分页条件（不变）                int pageSize = inParamDto.getPageSize() == null ? 10 : inParamDto.getPageSize();                int offset = (inParamDto.getPageNum() == null ? 1 : inParamDto.getPageNum() - 1) * pageSize;                sqlBuilder.append(" LIMIT ? OFFSET ? ");                currentParams.add(pageSize);                currentParams.add(offset);                // 执行查询                List<Map<String, Object>> sqlResult = this.poolDataSourceUtil.NpgNormal_GetListDicWithParams(sqlBuilder.toString(), currentParams);                result.addAll(sqlResult);            } finally {                currentParamHolder.remove();            }        }        return result;    }    // 全局缓存：key=schema.table，value=字段名→字段类型（numeric/varchar）    private Map<String, Map<String, String>> columnTypeCache = new ConcurrentHashMap<>();    /**     * 获取PostgreSQL表字段的真实数据类型（简化为 numeric 或 varchar）     *     * @param schema 数据库模式（如 public）     * @param table  表名（如 ps_ysg_point）     * @param column 字段名（如 dmgc、ysdm）     * @return 字段类型：numeric（数值类型）、varchar（字符串类型），默认varchar     */    private String getColumnType(String schema, String table, String column) {        String cacheKey = schema + "." + table;        Map<String, String> columnTypeMap = columnTypeCache.get(cacheKey);        // 缓存未命中时，查询information_schema获取字段类型        if (columnTypeMap == null) {            columnTypeMap = new HashMap<>();            String sql = "SELECT column_name, data_type " +                    "FROM information_schema.columns " +                    "WHERE table_schema = ? AND table_name = ?";            // 用JdbcTemplate查询系统表（根据你的数据库工具调整，确保能执行查询）            List<Map<String, Object>> columnList = poolDataSourceUtil.NpgNormal_GetListDic(sql, schema, table);            for (Map<String, Object> col : columnList) {                String colName = (String) col.get("column_name");                String colType = (String) col.get("data_type");                // 数值类型统一归类为 numeric（包括 integer、bigint、numeric、double precision）                if ("integer".equals(colType) || "bigint".equals(colType) || "numeric".equals(colType) || "double precision".equals(colType)) {                    columnTypeMap.put(colName, "numeric");                } else {                    // 其他类型（如 character varying、text）统一归类为 varchar                    columnTypeMap.put(colName, "varchar");                }            }            // 存入缓存，避免重复查询系统表            columnTypeCache.put(cacheKey, columnTypeMap);        }        // 返回字段类型，无匹配时默认varchar        return columnTypeMap.getOrDefault(column, "varchar");    }    // 优化表结构查询：使用缓存    private List<String> getTableColumns(String schema, String tableName) {        String cacheKey = schema + "." + tableName;        if (tableColumnsCache.containsKey(cacheKey)) {            Map<String, Object> cacheValue = tableColumnsCache.get(cacheKey);            long timestamp = (long) cacheValue.get("timestamp");            if (System.currentTimeMillis() - timestamp < CACHE_EXPIRE_MS) {                return (List<String>) cacheValue.get("columns");            }        }        String colSql = "SELECT column_name FROM information_schema.columns " +                "WHERE table_schema = ? AND table_name = ? AND column_name != 'shape'";        List<String> columns = poolDataSourceUtil.NpgNormal_GetListDic(colSql, schema, tableName)                .stream()                .map(col -> col.get("column_name").toString())                .collect(Collectors.toList());        Map<String, Object> cacheValue = new HashMap<>();        cacheValue.put("columns", columns);        cacheValue.put("timestamp", System.currentTimeMillis());        tableColumnsCache.put(cacheKey, cacheValue);        return columns;    }    // 坐标转换工具方法（供外部调用）//    public String getCoordinate4549(String text) {//        String sql = "select st_astext(st_transform(sde.st_geometry('" + text.replace("'", "''") + "',4490), 4528))";//        return poolDataSourceUtil.NpgNormal_GetScalar(sql).toString();//    }    public String getCoordinate4549(String text) {        // 验证输入参数是否为空        if (text == null || text.trim().isEmpty()) {            throw new IllegalArgumentException("输入的几何文本不能为空");        }        String sql = "select st_astext(st_transform(sde.st_geometry('" + text.replace("'", "''") + "',4490), 4528))";        String result = null;        try {            // 执行数据库查询并获取结果            Object scalar = poolDataSourceUtil.NpgNormal_GetScalar(sql);            // 处理查询结果为null的情况            if (scalar != null) {                result = scalar.toString();            } else {                // 可以返回空字符串或抛出特定异常，根据业务需求决定                result = null;                // 或者记录警告日志                // log.warn("坐标转换返回空结果，SQL: {}", sql);            }        } catch (NullPointerException e) {            // 捕获空指针异常（可能来自poolDataSourceUtil或查询结果处理）            throw new RuntimeException("坐标转换过程中发生空指针异常", e);        } catch (Exception e) {            // 捕获其他可能的异常            throw new RuntimeException("坐标转换失败", e);        }        return result;    }    /**     * TODO 对attributeSearch中的返回参数进行转义     * 处理结果列表：转换数值类型，保留特殊字符串（如时间戳）     * 所有表的 数字类型添加     * 所有 numeric 类型 + int4 类型字段的完整白名单     */    private static final List<String> NUMERIC_FIELD_WHITELIST = FieldConfig.getNumericFieldWhitelist();    public List<Map<String, Object>> processResultList(List<Map<String, Object>> resultList) {        List<Map<String, Object>> processedList = new ArrayList<>(resultList.size());        for (Map<String, Object> item : resultList) {            Map<String, Object> processedItem = new HashMap<>(item.size());            for (Map.Entry<String, Object> entry : item.entrySet()) {                String key = entry.getKey();                Object value = entry.getValue();                // 空值处理（不变）                if (value == null) {                    processedItem.put(key, null);                    continue;                }                String strValue = value.toString().trim();                // 空字符串处理（不变）                if (strValue.isEmpty()) {                    processedItem.put(key, strValue);                    continue;                }                // 核心逻辑1：特殊字段保持String类型（不变）                if (isPreserveAsString(key, strValue)) {                    processedItem.put(key, escapeSpecialChars(strValue)); // 安全转义                }                // 核心修改：仅白名单内的字段（xzb/yzb/dmgc）执行数字转义                else if (NUMERIC_FIELD_WHITELIST.contains(key) && isNumeric(strValue)) {                    try {                        processedItem.put(key, new BigDecimal(strValue)); // 转为BigDecimal                    } catch (NumberFormatException e) {                        // 数字格式异常时，仍保持String并转义（避免数据丢失）                        processedItem.put(key, escapeSpecialChars(strValue));                    }                }                // 核心逻辑3：其他字段（非白名单）保持String并转义（不变）                else {                    processedItem.put(key, escapeSpecialChars(strValue));                }            }            processedList.add(processedItem);        }        return processedList;    }    /**     * 判断是否为数字（整数或小数）     */    private boolean isNumeric(String value) {        return value.matches("^[-+]?\\d+$") || value.matches("^[-+]?\\d+\\.\\d+$");    }    /**     * 判断是否需要保持为String类型     * 1. 时间戳字段（如starttime）     * 2. 类似"20220507025410104"的17位数字字符串（时间格式）     */    private boolean isPreserveAsString(String key, String value) {        // 时间相关字段集合（使用Java 8支持的HashSet初始化）        Set<String> timeFields = new HashSet<>(Arrays.asList(                "starttime", "endtime", "gxsj", "inserttime",                "fqsj", "deletetime", "tcrq"        ));        if (timeFields.contains(key)) {            return true;        }        // 格式匹配：17位数字（如20220507025410104）        if (value.matches("^\\d{17}$")) {            return true;        }        return false;    }    /**     * 转义特殊字符，防止XSS攻击     */    private String escapeSpecialChars(String value) {        if (value == null) {            return null;        }        return value.replaceAll("&", "&amp;")                .replaceAll("<", "&lt;")                .replaceAll(">", "&gt;")                .replaceAll("\"", "&quot;")                .replaceAll("'", "&#39;");    }    /**     * 兼容Java 8的带超时异步任务（模拟orTimeout()功能）     */    private <T> CompletableFuture<T> supplyAsyncWithTimeout(            Supplier<T> supplier,            Executor executor,            ScheduledExecutorService scheduler,            long timeout,            TimeUnit unit) {        // 异步执行任务        CompletableFuture<T> future = CompletableFuture.supplyAsync(supplier, executor);        // 超时任务：若超时未完成，强制抛出TimeoutException        scheduler.schedule(() -> {            future.completeExceptionally(new TimeoutException("子任务超时（超时时间：" + timeout + unit.name().toLowerCase() + "）"));        }, timeout, unit);        return future;    }    /**     * 直接查询（支持全量/分页查询）     */    private TableDataInfo getBsmDateParallel(String startBsm, String endBsm, String tableName,                                             Integer pageSize, Integer pageNum) throws ExecutionException, InterruptedException {        String sql = buildPathSql(startBsm, endBsm, tableName);        System.out.println("根据连通性分析获取管线长度：sql" + sql);        List<Map<String, Object>> mapList = poolDataSourceUtil.connectivity(sql);        if (ObjectUtils.isNotEmpty(mapList)) {            return getDataTableByMyself(mapList, pageSize, pageNum);        } else {            return createErrorTableData("管线不联通！");        }    }    /**     * 构建路径查询SQL（保持原有SQL逻辑不变）     */    private String buildPathSql(String start, String end, String tableName) {        String sql =                "WITH RECURSIVE " +                        "params AS ( " +                        "    SELECT " +                        "        '" + start + "'::VARCHAR(32) AS p_bsm1, " +                        "        '" + end + "'::VARCHAR(32) AS p_bsm2 " +                        "), " +                        "existence_check AS ( " +                        "    SELECT " +                        "        EXISTS(SELECT 1 FROM " + tableName + " WHERE bsm = (SELECT p_bsm1 FROM params)) AS exists1, " +                        "        EXISTS(SELECT 1 FROM " + tableName + " WHERE bsm = (SELECT p_bsm2 FROM params)) AS exists2 " +                        "), " +                        "same_bsm_result AS ( " +                        "    SELECT l.*, 1 AS pos " +                        "    FROM " + tableName + " l, params p " +                        "    WHERE p.p_bsm1 = p.p_bsm2 AND l.bsm = p.p_bsm1 " +                        "), " +                        "pipeline_path AS ( " +                        "    SELECT " +                        "        bsm, " +                        "        qdkjbh, " +                        "        zdkjbh, " +                        "        ARRAY[bsm]::VARCHAR(32)[] AS visited, " +                        "        1 AS depth " +                        "    FROM " + tableName + ", params p " +                        "    WHERE bsm = p.p_bsm1 " +                        "    UNION ALL " +                        "    SELECT " +                        "        next.bsm, " +                        "        next.qdkjbh, " +                        "        next.zdkjbh, " +                        "        (path.visited || next.bsm)::VARCHAR(32)[] AS visited, " +                        "        path.depth + 1 AS depth " +                        "    FROM " + tableName + " next " +                        "    INNER JOIN pipeline_path path " +                        "        ON (next.qdkjbh = path.zdkjbh OR next.zdkjbh = path.qdkjbh) " +                        "        AND next.bsm <> ALL(path.visited) " +                        "        AND path.depth < 1000 " +                        "        AND path.bsm <> (SELECT p_bsm2 FROM params) " +                        "), " +                        "shortest_path AS ( " +                        "    SELECT visited " +                        "    FROM pipeline_path, params p " +                        "    WHERE bsm = p.p_bsm2 " +                        "    ORDER BY depth " +                        "    LIMIT 1 " +                        "), " +                        "path_result AS ( " +                        "    SELECT " +                        "        l.*, " +                        "        path.pos " +                        "    FROM " + tableName + " l " +                        "    JOIN ( " +                        "        SELECT " +                        "            unnest(visited) AS bsm, " +                        "            generate_subscripts(visited, 1) AS pos " +                        "        FROM shortest_path " +                        "        WHERE visited IS NOT NULL " +                        "    ) AS path ON l.bsm = path.bsm " +                        "), " +                        "all_results AS ( " +                        "    SELECT * FROM same_bsm_result " +                        "    WHERE EXISTS (SELECT 1 FROM same_bsm_result) " +                        "    UNION ALL " +                        "    SELECT * FROM path_result " +                        "    WHERE EXISTS (SELECT 1 FROM existence_check WHERE exists1 AND exists2) " +                        "      AND NOT EXISTS (SELECT 1 FROM same_bsm_result) " +                        "    ORDER BY pos " +                        "), " +                        "filtered_data AS ( " +                        "    SELECT " +                        "        *, " +                        "        LAG(zdkjbh) OVER (ORDER BY pos) AS prev_zdkjbh, " +                        "        LAG(qdkjbh) OVER (ORDER BY pos) AS prev_qdkjbh, " +                        "        LEAD(zdkjbh) OVER (ORDER BY pos) AS next_zdkjbh, " +                        "        LEAD(qdkjbh) OVER (ORDER BY pos) AS next_qdkjbh " +                        "    FROM all_results " +                        ")" +                        "SELECT " +                        "    a.*, " +                        "    a.xzq AS ssqy, " +                        "    a.zydw AS tcdw, " +                        "    st_astext(st_transform(sde.st_geometry(st_astext(shape)::TEXT, 4528), 4490)) AS shapetext " +                        "FROM filtered_data a " +                        "WHERE NOT ( " +                        "    prev_zdkjbh IS NOT NULL " +                        "    AND next_zdkjbh IS NOT NULL " +                        "    AND prev_qdkjbh IS NOT NULL " +                        "    AND next_qdkjbh IS NOT NULL " +                        "    AND (prev_zdkjbh = next_zdkjbh OR prev_qdkjbh = next_qdkjbh) " +                        ") " +                        "ORDER BY pos;";        return sql;    }    /**     * 分页处理方法（支持全量返回：当pageSize和pageNum均为null时返回全部数据）     */    public TableDataInfo getDataTableByMyself(List<?> list, Integer pageSize, Integer pageNum) {        List<?> dataList = (list == null) ? Collections.emptyList() : list;        long total = dataList.size();        // 若pageSize和pageNum均为null，返回全量数据        if (pageSize == null && pageNum == null) {            TableDataInfo rspData = new TableDataInfo();            rspData.setCode(HttpStatus.SUCCESS);            rspData.setMsg("查询成功（全量数据）");            rspData.setData(dataList);            rspData.setTotal(total);            return rspData;        }        // 否则按原逻辑处理分页（参数默认值）        pageSize = (pageSize == null || pageSize <= 0) ? 10 : pageSize;        pageNum = (pageNum == null || pageNum <= 0) ? 1 : pageNum;        int start = Math.max((pageNum - 1) * pageSize, 0);        List<?> pageData = dataList.stream()                .skip(start)                .limit(pageSize)                .collect(Collectors.toList());        TableDataInfo rspData = new TableDataInfo();        rspData.setCode(HttpStatus.SUCCESS);        rspData.setMsg("查询成功（分页数据）");        rspData.setData(pageData);        rspData.setTotal(total);        return rspData;    }    /**     * 创建错误结果     */    private TableDataInfo createErrorTableData(String message) {        TableDataInfo error = new TableDataInfo();        error.setCode(HttpStatus.ERROR);        error.setMsg(message);        error.setData(Collections.emptyList());        error.setTotal(0L);        return error;    }    /**     * 获取两个点位的数据（保持原有SQL逻辑不变）     */    public List<Map<String, Object>> queryNearestLinesOne(String firstPoslon, String firstPoslat, String tableName) {        if (firstPoslon == null || firstPoslat == null ||                firstPoslon.trim().isEmpty() || firstPoslat.trim().isEmpty()) {            throw new IllegalArgumentException("经纬度参数不能为空");        }        try {            String lon = firstPoslon.trim();            String lat = firstPoslat.trim();            if (!tableName.matches("^[a-zA-Z0-9_]+$")) {                throw new IllegalArgumentException("表名格式非法: " + tableName);            }            // 保持原有SQL逻辑不变            String sql = "WITH input_point AS ( SELECT ST_Transform ( ST_SetSRID(ST_MakePoint(" + lon + ", " + lat + "), 4490), 4528 ) AS geom ),\n" +                    "converted_lines AS ( SELECT bsm, ST_GeomFromWKB ( ST_AsBinary ( shape ), 4528 ) AS geom_line FROM " + tableName + " ),\n" +                    "nearest_line AS (\n" +                    "  SELECT\n" +                    "    l.bsm,\n" +                    "    ST_AsText ( ST_ClosestPoint ( l.geom_line, ip.geom ) ) AS proj_pt_wkt \n" +                    "  FROM\n" +                    "    converted_lines l,\n" +                    "    input_point ip \n" +                    "  ORDER BY\n" +                    "    ST_Distance ( l.geom_line, ip.geom ) \n" +                    "    LIMIT 1 \n" +                    "  ) SELECT\n" +                    "  bsm,\n" +                    "  ST_X ( ST_Geometry ( proj_pt_wkt, 4549 ) ) :: TEXT AS x,\n" +                    "  ST_Y ( ST_Geometry ( proj_pt_wkt, 4549 ) ) :: TEXT AS y \n" +                    "FROM\n" +                    "  nearest_line;";            return poolDataSourceUtil.NpgNormal_GetListDic(sql);        } catch (Exception e) {            throw new RuntimeException("查询失败: " + e.getMessage(), e);        }    }    /**     * 基于连通性分析，获取管线距离     * TODO 接口优化     *     * @param dto     * @return     */    public Map<String, Object> queryPipelineLength(QueryPipelineLengthDTO dto) {        // 设置超时时间（秒）        final int TOTAL_TIMEOUT = 60;        final int SUB_TASK_TIMEOUT = 30;        // 定义线程池（复用线程，避免频繁创建）        ExecutorService executor = Executors.newFixedThreadPool(2);        ScheduledExecutorService scheduler = Executors.newSingleThreadScheduledExecutor();        try {            // 1. 并行查询起点和终点的线数据（带超时）            CompletableFuture<List<Map<String, Object>>> startFuture = supplyAsyncWithTimeout(                    () -> queryNearestLinesOne(dto.getFirstPoslon(), dto.getFirstPoslat(), dto.getTables().get(0)),                    executor,                    scheduler,                    SUB_TASK_TIMEOUT,                    TimeUnit.SECONDS            );            CompletableFuture<List<Map<String, Object>>> endFuture = supplyAsyncWithTimeout(                    () -> queryNearestLinesOne(dto.getEndPoslon(), dto.getEndPoslat(), dto.getTables().get(0)),                    executor,                    scheduler,                    SUB_TASK_TIMEOUT,                    TimeUnit.SECONDS            );            // 2. 等待所有查询完成（总超时控制）            CompletableFuture.allOf(startFuture, endFuture).get(TOTAL_TIMEOUT, TimeUnit.SECONDS);            // 3. 获取并校验起点/终点结果            List<Map<String, Object>> mapListStart = startFuture.get();            List<Map<String, Object>> mapListEnd = endFuture.get();            if (ObjectUtils.isEmpty(mapListStart)) {                return createErrorMap("未找到起点对应的线数据", 404);            }            if (ObjectUtils.isEmpty(mapListEnd)) {                return createErrorMap("未找到终点对应的线数据", 404);            }            // 4. 提取起点/终点的坐标和标识码            Map<String, Object> startMap = mapListStart.get(0);            String startX = String.valueOf(startMap.getOrDefault("x", "")); // 避免null值直接转换            String startY = String.valueOf(startMap.getOrDefault("y", ""));            String bsmStart = String.valueOf(startMap.getOrDefault("bsm", ""));            Map<String, Object> endMap = mapListEnd.get(0);            String endX = String.valueOf(endMap.getOrDefault("x", ""));            String endY = String.valueOf(endMap.getOrDefault("y", ""));            String bsmEnd = String.valueOf(endMap.getOrDefault("bsm", ""));            // 校验关键参数非空            if (StringUtils.isEmpty(bsmStart) || StringUtils.isEmpty(bsmEnd)) {                return createErrorMap("起点或终点的标识码(bsm)为空", 400);            }            String tableName = dto.getTables().get(0);            // 5. 执行全量连通性分析（传null表示查询全部数据）            TableDataInfo bsmDateParallel = getBsmDateParallel(bsmStart, bsmEnd, tableName, null, null);            if (ObjectUtils.isEmpty(bsmDateParallel)) {                return createErrorMap("连通性分析结果为空", 500);            }            // 6. 提取所有连通管线的bsm列表            List<String> bsmList = new ArrayList<>();            List<?> dataList = bsmDateParallel.getData();            if (ObjectUtils.isNotEmpty(dataList)) {                for (Object datum : dataList) {                    // 严格校验数据类型，避免类型转换异常                    if (!(datum instanceof Map)) {                        log.warn("连通性数据格式异常，非Map类型：{}", datum);                        continue;                    }                    Map<String, Object> dataMap = (Map<String, Object>) datum;                    Object bsmObj = dataMap.get("bsm");                    if (bsmObj != null) {                        bsmList.add(bsmObj.toString());                    } else {                        log.warn("连通性数据中存在bsm为null的记录");                    }                }            }            // 7. 校验bsm列表非空（避免后续计算异常）            if (bsmList.isEmpty()) {                return createErrorMap("未查询到连通的管线数据（bsm列表为空）", 404);            }            // 8. 计算长度并获取GeoJson数据            Map<String, Object> resultMap = calculatePipeLength(startX, startY, endX, endY, bsmList, tableName);            resultMap.put("geoJsonData", getGeoJson(tableName, bsmList));            resultMap.put("code", 200);            resultMap.put("msg", "查询成功");            return resultMap;        } catch (TimeoutException e) {            return createErrorMap("查询超时，请稍后重试（超时时间：" + TOTAL_TIMEOUT + "秒）", 504);        } catch (InterruptedException e) {            Thread.currentThread().interrupt(); // 恢复中断状态            return createErrorMap("查询被中断：" + e.getMessage(), 500);        } catch (ExecutionException e) {            Throwable cause = e.getCause();            String errorMsg = cause instanceof TimeoutException                    ? "子任务查询超时（超时时间：" + SUB_TASK_TIMEOUT + "秒）"                    : "查询失败：" + (cause != null ? cause.getMessage() : e.getMessage());            return createErrorMap(errorMsg, 500);        } catch (Exception e) {            return createErrorMap("系统异常：" + e.getMessage(), 500);        } finally {            // 安全关闭线程池，避免资源泄漏            executor.shutdownNow();            scheduler.shutdownNow();            try {                // 等待线程池终止                if (!executor.awaitTermination(5, TimeUnit.SECONDS)) {                    log.warn("线程池未正常关闭，强制终止剩余任务");                }                if (!scheduler.awaitTermination(5, TimeUnit.SECONDS)) {                    log.warn("定时线程池未正常关闭，强制终止剩余任务");                }            } catch (InterruptedException e) {                Thread.currentThread().interrupt();            }        }    }    /**     * 创建统一格式的错误返回Map     *     * @param message 错误信息     * @param code    错误码（建议遵循HTTP状态码规范）     * @return 错误Map     */    private Map<String, Object> createErrorMap(String message, int code) {        Map<String, Object> errorMap = new HashMap<>(3);        errorMap.put("code", code);        errorMap.put("msg", message);        errorMap.put("data", null);        return errorMap;    }    /**     * 返回geoJson结果封装     * 合并获取和解析geoJson的逻辑，直接重复调用     *     * @param tableName 表名     * @param bsmList   BSM列表     * @return 解析后的geoJson Map对象     */    public Map<String, Object> getGeoJson(String tableName, List<String> bsmList) {        Map<String, Object> objectMapGeoJson = geoJson(tableName, bsmList);        if (objectMapGeoJson == null || !objectMapGeoJson.containsKey("row_to_json")) {            return new HashMap<>();        }        String json = objectMapGeoJson.get("row_to_json").toString();        try {            return objectMapper.readValue(json, new TypeReference<Map<String, Object>>() {            });        } catch (Exception e) {            e.printStackTrace();            return new HashMap<>();        }    }    /**     * geojson 转换     *     * @param tableName     * @param bsmList     * @return     */    public Map<String, Object> geoJson(String tableName, List<String> bsmList) {        // 处理空列表：直接返回空结果        if (bsmList == null || bsmList.isEmpty()) {            return Collections.emptyMap();        }        // 构建BSM的IN条件（处理单引号转义，防止SQL注入）        String bsmInClause = bsmList.stream()                .map(bsm -> bsm.trim().replace("'", "''"))                .collect(Collectors.joining("','"));        bsmInClause = "'" + bsmInClause + "'";        String sql = "WITH all_points AS (\n" +                "  SELECT\n" +                "    ( ST_DumpPoints ( ST_GeomFromText ( st_astext ( shape ) :: TEXT, 4528 ) ) ).geom AS pt \n" +                "        FROM " + tableName + " " +                "        WHERE bsm IN(" + bsmInClause + ") " +                "  ),\n" +                "  ordered_points AS ( SELECT pt FROM all_points ORDER BY ST_X ( pt ), ST_Y ( pt ) ) SELECT\n" +                "  row_to_json ( fc ) \n" +                "FROM\n" +                "  (\n" +                "  SELECT\n" +                "    'FeatureCollection' AS TYPE,\n" +                "    array_to_json (\n" +                "      ARRAY [ (\n" +                "      SELECT\n" +                "        row_to_json ( f ) \n" +                "      FROM\n" +                "        ( SELECT 'Feature' AS TYPE, ( st_asgeojson ( st_transform ( ST_MakeLine ( pt ), 4490 ) ) ) :: JSON AS geometry FROM ordered_points ) f \n" +                "      ) ] \n" +                "    ) AS features \n" +                "  ) fc;";        // 执行查询并返回结果        return poolDataSourceUtil.NpgNormal_GetListDicNo1(sql);    }    /**     * 计算两点之间的管段长度     *     * @param firstlon 起点经度  40509483.3933618     * @param firstlat 起点纬度 3490986.49281043     * @param endlon   终点经度  40509483.3933618     * @param endlat   终点纬度  3490986.49281043     * @param bsmList  管段标识列表（如 ["bsm1", "bsm2", "bsm3"...]）     * @return 包含长度信息的结果集     */    public Map<String, Object> calculatePipeLength(            String firstlon, String firstlat,            String endlon, String endlat,            List<String> bsmList, String tableName) {        // 校验入参        if (firstlon == null || firstlat == null || endlon == null || endlat == null                || bsmList == null || bsmList.isEmpty()) {            throw new IllegalArgumentException("投影坐标参数和管段列表不能为空");        }        try {            // 1. 从 POINT(...) 格式中提取纯数字坐标（返回字符串类型）            String x1 = extractCoordinate(firstlon, "起点经度");            String y1 = extractCoordinate(firstlat, "起点纬度");            String x2 = extractCoordinate(endlon, "终点经度");            String y2 = extractCoordinate(endlat, "终点纬度");            // 校验表名格式            if (!tableName.matches("^[a-zA-Z0-9_]+$")) {                throw new IllegalArgumentException("表名格式非法，只能包含字母、数字和下划线: " + tableName);            }            // 2. 处理管段列表，拼接为 'bsm1','bsm2' 格式            String bsmInClause = bsmList.stream()                    .map(bsm -> bsm.trim().replace("'", "''"))                    .collect(Collectors.joining("','"));            bsmInClause = "'" + bsmInClause + "'";            // 3. 构建 SQL（使用字符串拼接，避免 %f 格式符）            String sql = "WITH input_points AS (" +                    "  SELECT" +                    "    ST_SetSRID(ST_MakePoint(" + x1 + ", " + y1 + "), 4528) AS pt_start," +  // 直接拼接字符串                    "    ST_SetSRID(ST_MakePoint(" + x2 + ", " + y2 + "), 4528) AS pt_end" +                    "), " +                    "converted_lines AS (" +                    "  SELECT" +                    "    bsm," +                    "    qddh," +                    "    zddh," +                    "    ST_GeomFromEWKT('SRID=4528;' || ST_AsText(shape)) AS geom_line" +                    "  FROM " + tableName +                    "  WHERE bsm IN (" + bsmInClause + ")" +                    "), " +                    "merged_line AS (" +                    "  SELECT ST_LineMerge(ST_Union(geom_line)) AS geom_line" +                    "  FROM converted_lines" +                    "), " +                    "locates AS (" +                    "  SELECT" +                    "    ip.pt_start," +                    "    ip.pt_end," +                    "    ml.geom_line," +                    "    ST_LineLocatePoint(ml.geom_line, ip.pt_start) AS frac_start," +                    "    ST_LineLocatePoint(ml.geom_line, ip.pt_end) AS frac_end," +                    "    ST_Length(ml.geom_line) AS total_length" +                    "  FROM input_points ip, merged_line ml" +                    ")" +                    " SELECT " +                    "  abs(frac_end - frac_start) * total_length AS pipeLength" +                    " FROM locates";            return poolDataSourceUtil.NpgNormal_GetListDicNo1(sql);        } catch (NumberFormatException e) {            throw new IllegalArgumentException("投影坐标格式错误，无法转换为数字: " + e.getMessage(), e);        } catch (Exception e) {            throw new RuntimeException("计算管段长度失败: " + e.getMessage(), e);        }    }    /**     * 从 POINT(...) 格式的字符串中提取坐标数值     * 例如："POINT(40509497.1665317 3490973.52221043)" → 提取数值部分     */    private String extractCoordinate(String wktStr, String paramName) {        if (wktStr == null || wktStr.trim().isEmpty()) {            throw new IllegalArgumentException(paramName + "不能为空");        }        String cleaned = wktStr.trim()                .replaceAll("[^0-9\\.\\- ]", "")                .trim();        String[] parts = cleaned.split("\\s+");        if (parts.length < 1) {            throw new NumberFormatException(paramName + "格式错误，无法提取坐标: " + wktStr);        }        return (parts[0]);    }    /**     * TODO 纵断面分析 接口业务逻辑     * 主函数 （querySectionAnalysis）     */    public static final int INT = 2;    public static final int INT1 = 180;    private static final int CPU_CORES = Runtime.getRuntime().availableProcessors();    private static final int DB_CONCURRENT_LIMIT = 20; // 数据库并发查询上限（不超过连接池容量）    private static final ExecutorService mainExecutor = new ThreadPoolExecutor(            CPU_CORES * 5,            CPU_CORES * 10,            60L, TimeUnit.SECONDS,            new LinkedBlockingQueue<>(CPU_CORES * 5 * 20), // 动态队列容量            new ThreadPoolExecutor.CallerRunsPolicy()    );    // 限制数据库查询并发的线程池    private static final ExecutorService dbExecutor = new ThreadPoolExecutor(            DB_CONCURRENT_LIMIT,            DB_CONCURRENT_LIMIT,            60L, TimeUnit.SECONDS,            new LinkedBlockingQueue<>(1000),            new ThreadPoolExecutor.CallerRunsPolicy()    );    /**     * 管线断截面分析入口（移除缓存版）     */    public SectionAnalysisVo querySectionAnalysis(SectionAnalysisDto sectionAnalysisDto) {        long totalStart = System.currentTimeMillis();        // 1. 处理坐标系参数        sectionAnalysisDto.setCoordinate("'SRID=" + sectionAnalysisDto.getCoordinate() + ";'");        // 2. 解密结果参数        if (!StringUtils.isEmpty(sectionAnalysisDto.getResult())) {            String res = AesUtil.decrypt(sectionAnalysisDto.getResult());            sectionAnalysisDto.setResult(res);        }        // 3. 查询初始管线（单条查询，强制使用索引）        String initialLineSql = buildInitialLineSql(sectionAnalysisDto);        SectionLine initialLine = BeanUtil.mapToBean(                queryDb(initialLineSql).stream().findFirst().orElse(null),                SectionLine.class, true        );        if (initialLine.getObjectid() == null) {            return null;        }        // 4. 初始化已处理ID集合（线程安全Set）        Set<Integer> gxIds = Sets.newConcurrentHashSet();        gxIds.add(initialLine.getObjectid());        Set<Integer> gdIds = Sets.newConcurrentHashSet();        // 5. 计算初始管线参数（无缓存）        initialLine.setCoor(sectionAnalysisDto.getCoordinate());        if (sectionAnalysisDto.getPointId() != null) {            initialLine.setPointid(sectionAnalysisDto.getPointId());        }        calculateLineParams(initialLine);        // 6. 批量查询初始管线关联的点        List<SectionPoint> initialPoints = queryInitialPoints(initialLine, gdIds);        gdIds.addAll(initialPoints.stream()                .map(SectionPoint::getObjectid)                .filter(Objects::nonNull)                .collect(Collectors.toList()));        // 7. 初始化结果集合        Queue<SectionLine> sectionLineQueue = new ConcurrentLinkedQueue<>();        sectionLineQueue.add(initialLine);        Queue<SectionPoint> resultPointQueue = new ConcurrentLinkedQueue<>();        // 8. 迭代处理        processIteratively(initialPoints, sectionLineQueue, resultPointQueue,                sectionAnalysisDto, gxIds, gdIds, sectionAnalysisDto.getSectionNum());        // 9. 组装结果        SectionAnalysisVo result = new SectionAnalysisVo();        result.setSectionLineList(new ArrayList<>(sectionLineQueue));//        result.setSectionPointList(new ArrayList<>(resultPointQueue));        System.out.println("总耗时：" + (System.currentTimeMillis() - totalStart) + "ms");        return result;    }    /**     * 迭代处理（替代递归）     */    private void processIteratively(            List<SectionPoint> initialPoints,            Queue<SectionLine> sectionLineQueue,            Queue<SectionPoint> resultPointQueue,            SectionAnalysisDto dto,            Set<Integer> gxIds,            Set<Integer> gdIds,            int maxDepth) {        // 用队列存储每层的点和当前深度        Queue<PointDepth> processQueue = new LinkedList<>();        processQueue.add(new PointDepth(initialPoints, 1));        while (!processQueue.isEmpty()) {            PointDepth pd = processQueue.poll();            List<SectionPoint> currentPoints = pd.points;            int currentDepth = pd.depth;            if (currentDepth > maxDepth) {                continue; // 超过最大深度，停止处理            }            // 并行处理当前层的点            List<CompletableFuture<List<SectionPoint>>> childFutures = currentPoints.stream()                    .map(point -> CompletableFuture.supplyAsync(() -> {                        return processSinglePoint(                                point, dto, currentDepth, maxDepth,                                sectionLineQueue, resultPointQueue, gxIds, gdIds                        );                    }, mainExecutor))                    .collect(Collectors.toList());            // 收集所有子点，进入下一层迭代            CompletableFuture.allOf(childFutures.toArray(new CompletableFuture[0])).join();            List<SectionPoint> allChildPoints = new ArrayList<>();            for (CompletableFuture<List<SectionPoint>> future : childFutures) {                allChildPoints.addAll(future.join());            }            if (!allChildPoints.isEmpty()) {                processQueue.add(new PointDepth(allChildPoints, currentDepth + 1));            }        }    }    /**     * 处理单个点（返回子点用于下一层迭代）     */    private List<SectionPoint> processSinglePoint(            SectionPoint point,            SectionAnalysisDto dto,            int currentDepth,            int maxDepth,            Queue<SectionLine> sectionLineQueue,            Queue<SectionPoint> resultPointQueue,            Set<Integer> gxIds,            Set<Integer> gdIds) {        point.setCurrentIndex(currentDepth);        // 超过最大深度，标记为终点        if (currentDepth >= maxDepth) {            point.setEndFlag(1);            resultPointQueue.add(point);            return Collections.emptyList();        }        // 设置点坐标信息        point.setCoor(dto.getCoordinate());        point.setCoorNum(dto.getCoordinate());        // 1. 查询关联管线        List<SectionLine> relatedLines = queryRelatedLines(point, gxIds, dto);        if (CollectionUtils.isEmpty(relatedLines)) {            point.setEndFlag(1);            resultPointQueue.add(point);            return Collections.emptyList();        }        // 2. 标记已处理管线        relatedLines.stream()                .map(SectionLine::getObjectid)                .filter(Objects::nonNull)                .forEach(gxIds::add);        // 3. 计算管线参数（无缓存）        relatedLines.forEach(line -> {            calculateLineParams(line);            line.setCoor(dto.getCoordinate());            sectionLineQueue.add(line);        });        // 4. 添加当前点到结果        resultPointQueue.add(point);        // 5. 批量查询关联点        List<SectionPoint> childPoints = new ArrayList<>();        if (!relatedLines.isEmpty()) {            childPoints = queryBatchRelatedPoints(relatedLines, gdIds, dto);            // 标记已处理点            childPoints.stream()                    .map(SectionPoint::getObjectid)                    .filter(Objects::nonNull)                    .forEach(gdIds::add);        }        return childPoints;    }    /**     * 批量查询关联点（减少DB交互）     */    private List<SectionPoint> queryBatchRelatedPoints(            List<SectionLine> lines, Set<Integer> gdIds, SectionAnalysisDto dto) {        if (CollectionUtils.isEmpty(lines)) {            return Collections.emptyList();        }        // 构建批量查询SQL（用UNION ALL拼接）        StringBuilder sql = new StringBuilder("select * from (\n");        for (int i = 0; i < lines.size(); i++) {            SectionLine line = lines.get(i);            sql.append("select ")                    .append(line.getObjectid()).append(" as line_id, ") // 标记所属管线ID                    .append("objectid, bsm as bh, dmgc, ")                    .append("replace('").append(line.getTypeName()).append("','point','line') as typeName, ")                    .append("round(sde.st_x(shape)::numeric,8) as \"point_x\", ")                    .append("round(sde.st_y(shape)::numeric,8) as \"point_y\", ")                    .append("sde.st_astext(shape) as \"coordinate\", ")                    .append("sde.st_astext(sde.st_envelope(sde.st_buffer(shape, 0.01))) as \"shape_buf\"\n")                    .append("from ").append(line.getTypeName()).append("\n")                    .append("where sde.st_intersects(sde.st_geometry('").append(line.getShapeBuf()).append("',").append(srid).append("), shape)\n");            // 排除指定点ID            if (line.getPointid() != null && !"".equals(line.getPointid().toString()) && !"null".equals(line.getPointid().toString())) {                sql.append("and objectid !=").append(line.getPointid()).append("\n");            }            // 坐标匹配条件            sql.append("and ((round(sde.st_x(shape)::numeric,8)=").append(line.getStartPointX()).append(" and round(sde.st_y(shape)::numeric,8)=").append(line.getStartPointY()).append(") ")                    .append("or (round(sde.st_x(shape)::numeric,8)=").append(line.getEndPointX()).append(" and round(sde.st_y(shape)::numeric,8)=").append(line.getEndPointY()).append("))\n");            if (i != lines.size() - 1) {                sql.append("union all\n");            }        }        sql.append(") t1 where 1=1 ");        // 排除已处理点        if (!gdIds.isEmpty()) {            sql.append("and objectid not in (").append(gdIds.stream().map(String::valueOf).collect(Collectors.joining(","))).append(")");        }        // 执行查询并转换结果        List<Map<String, Object>> resultList = queryDb(sql.toString());        return resultList.stream()                .map(map -> JSON.parseObject(JSON.toJSONString(map), SectionPoint.class))                .collect(Collectors.toList());    }// -------------------------- 数据库查询与SQL构建 --------------------------    /**     * 执行DB查询（带超时和并发控制）     */    private List<Map<String, Object>> queryDb(String sql) {        try {            return CompletableFuture.supplyAsync(() -> {                long start = System.currentTimeMillis();                List<Map<String, Object>> result = poolDataSourceUtil.NpgNormal_GetListDic(sql);                System.out.println("result:{}" + result);                long cost = System.currentTimeMillis() - start;                if (cost > 500) { // 打印慢查询                    System.out.println("慢查询(" + cost + "ms)：" + sql);                }                return result;            }, dbExecutor).get(30, TimeUnit.SECONDS); // 单个查询超时30秒        } catch (Exception e) {            throw new RuntimeException("DB查询失败：" + sql, e);        }    }    /**     * 构建初始管线查询SQL（强制使用空间索引）     */    private String buildInitialLineSql(SectionAnalysisDto dto) {        return new StringBuilder()                .append("select /*+ INDEX(typename idx_shape) */\n") // 强制索引                .append("  typename.bsm as bh, typename.*,\n")                .append("  sde.st_length(typename.shape) as \"horizontalgdcd\",\n")                .append("  replace('").append(dto.getTypeName()).append("','line','point') as \"typeName\",\n")                .append("  round(sde.st_x(sde.st_startpoint(typename.shape))::numeric,8) as \"start_point_x\",\n")                .append("  round(sde.st_y(sde.st_startpoint(typename.shape))::numeric,8) as \"start_point_y\",\n")                .append("  round(sde.st_x(sde.st_endpoint(typename.shape))::numeric,8) as \"end_point_x\",\n")                .append("  round(sde.st_y(sde.st_endpoint(typename.shape))::numeric,8) as \"end_point_y\",\n")                .append("  sde.st_astext(typename.shape) as coordinate,\n")                .append("  sde.st_astext(sde.ST_Centroid(typename.shape)) as point,\n")                .append("  sde.st_astext(sde.st_envelope(sde.st_buffer(typename.shape, 0.01))) as \"shape_buf\"\n")                .append("from ").append(dto.getTypeName()).append(" as typename\n")                .append("where typename.bsm='").append(dto.getFeaturecode()).append("'")                .toString();    }    /**     * 查询初始管线关联的点（复用批量查询逻辑）     */    private List<SectionPoint> queryInitialPoints(SectionLine line, Set<Integer> gdIds) {        return queryBatchRelatedPoints(Collections.singletonList(line), gdIds, null);    }    /**     * 查询与当前点关联的管线（带索引优化）     */    private List<SectionLine> queryRelatedLines(SectionPoint point, Set<Integer> gxIds, SectionAnalysisDto dto) {        StringBuilder sql = new StringBuilder()                .append("select /*+ INDEX(typename idx_shape) */ * from (\n") // 强制索引                .append("select\n")                .append("typename.bsm as bh, typename.*,\n")                .append("sde.st_length(typename.shape) as \"horizontalgdcd\",\n")                .append(point.getObjectid()).append(" as pointid,\n")                .append("replace('").append(point.getTypeName()).append("','line','point') as typeName,\n")                .append("round(sde.st_x(sde.st_startpoint(typename.shape))::numeric,8) as \"start_point_x\",\n")                .append("round(sde.st_y(sde.st_startpoint(typename.shape))::numeric,8) as \"start_point_y\",\n")                .append("round(sde.st_x(sde.st_endpoint(typename.shape))::numeric,8) as \"end_point_x\",\n")                .append("round(sde.st_y(sde.st_endpoint(typename.shape))::numeric,8) as \"end_point_y\",\n")                .append("sde.st_astext(typename.shape) as \"coordinate\",\n")                .append("sde.st_astext(sde.ST_Centroid(typename.shape)) as point,\n")                .append("sde.st_astext(sde.st_envelope(sde.st_buffer(typename.shape, 0.01))) as \"shape_buf\"\n")                .append("from ").append(point.getTypeName()).append(" as typename\n")                .append("where\n")                .append("sde.st_intersects(sde.st_geometry('").append(point.getShapeBuf()).append("',").append(srid).append("),typename.shape)\n")                .append("and typename.objectid !=").append(point.getLineid()).append("\n")                .append(") t1\n")                .append("where\n")                .append("((start_point_x=").append(point.getPointX()).append(" and start_point_y=").append(point.getPointY()).append(")\n")                .append("or\n")                .append("(end_point_x=").append(point.getPointX()).append(" and end_point_y=").append(point.getPointY()).append("))");        // 排除已处理管线        if (!gxIds.isEmpty()) {            sql.append(" and objectid not in(").append(gxIds.stream().map(String::valueOf).collect(Collectors.joining(","))).append(")");        }        List<Map<String, Object>> resultList = queryDb(sql.toString());        return resultList.stream()                .map(map -> JSON.parseObject(JSON.toJSONString(map), SectionLine.class))                .collect(Collectors.toList());    }// -------------------------- 工具方法 --------------------------    /**     * 计算管线参数（移除缓存逻辑）     */    private void calculateLineParams(SectionLine line) {        // 直接计算，不查询缓存        if (line.getQdgc() == null || line.getZdgc() == null || line.getHorizontalgdcd() == null) {            return;        }        BigDecimal gc = line.getZdgc().subtract(line.getQdgc()).abs();        BigDecimal horizontalgdcd = line.getHorizontalgdcd();        // 计算实际长度        double realLength = Math.sqrt(                Math.pow(gc.doubleValue(), INT) +                        Math.pow(horizontalgdcd.doubleValue(), INT)        );        line.setRealgdcd(BigDecimal.valueOf(realLength));        // 计算坡度（避免除零）        if (horizontalgdcd.compareTo(BigDecimal.ZERO) != 0) {            BigDecimal slopeRatio = gc.divide(horizontalgdcd, 6, RoundingMode.CEILING);            double slopeAngle = Math.atan(slopeRatio.doubleValue()) * INT1 / Math.PI;            line.setGradient(BigDecimal.valueOf(slopeAngle));        }    }    /**     * 辅助类：存储点集合和当前深度     */    private static class PointDepth {        List<SectionPoint> points;        int depth;        PointDepth(List<SectionPoint> points, int depth) {            this.points = points;            this.depth = depth;        }    }    /**     * 服务销毁时关闭线程池     */    @PreDestroy    public void destroy() {        mainExecutor.shutdown();        dbExecutor.shutdown();        try {            if (!mainExecutor.awaitTermination(5, TimeUnit.SECONDS)) {                mainExecutor.shutdownNow();            }            if (!dbExecutor.awaitTermination(5, TimeUnit.SECONDS)) {                dbExecutor.shutdownNow();            }        } catch (InterruptedException e) {            mainExecutor.shutdownNow();            dbExecutor.shutdownNow();        }    }    /**     * 横断面分析 notes = "【v1.0.2】 横断面分析，返回与传入线相交的管线的各种计算数据"     * TODO 资源管理优化     *     * @param req     * @return     * @throws Exception     */    public List<CrossSectionAnalysisVo> queryCrossSectionAnalysis(MultiTypeReqDto req) throws Exception {        if (req.getGeoCompareModels().size() > 0) {            for (GeoCompareModel geo : req.getGeoCompareModels()) {                if (com.njcky.utils.page.StringUtils.isNotEmpty(geo.getGeoValueFilter().getGeoValue())) {                    geo.getGeoValueFilter().setGeoValue(getCoordinate4549(geo.getGeoValueFilter().getGeoValue()));                }            }        }        String filterSQL = req.generateSqlFilter();        if (!org.springframework.util.StringUtils.isEmpty(req.getResult())) {            String res = AesUtil.decrypt(req.getResult());            filterSQL = filterSQL + res;        }        if (com.njcky.utils.page.StringUtils.isEmpty(filterSQL)) {            throw new CustomException("invalid params");        }        List<String> typeNames = req.getTypeNames();        List<CrossSectionAnalysisVo> sectionAnalysisVoList = new ArrayList<>();        String sql = filterSQL;        typeNames.parallelStream().forEach(name -> {            String sqlStr = "select a.*, '" + name + "' as typeName, sde.st_astext(shape) as coordinate,sde.st_astext(sde.ST_Centroid(shape)) as point\n" +                    "        from " + name + " as a\n" +                    "        where " + sql + " order by a.objectid";            sqlStr = sqlStr.replace("public", "sde").replace("st_geomfromtext", "st_geometry");            //        //        System.out.println(sql);(sqlStr);            List<CrossSectionAnalysisVo> list = poolDataSourceUtil.NpgNormal_GetListDic(sqlStr).stream()                    .map(map -> JSON.parseObject(JSON.toJSONString(map), CrossSectionAnalysisVo.class))                    .collect(Collectors.toList());            sectionAnalysisVoList.addAll(list);        });        GeometryFactory geometryFactory = JTSFactoryFinder.getGeometryFactory(null);        WKTReader reader = new WKTReader(geometryFactory);        //        //        System.out.println(sql);("req:" + req.getGeoCompareModels());        String spatialDtoWKT = req.getGeoCompareModels().get(0).getGeoValueFilter().getGeoValue();        //        //        System.out.println(sql);(spatialDtoWKT);        for (CrossSectionAnalysisVo crossSectionAnalysisVo : sectionAnalysisVoList) {            Geometry gx1Geom = reader.read(spatialDtoWKT);            LineString line1 = null;            if (gx1Geom instanceof MultiLineString) {                MultiLineString var2 = (MultiLineString) gx1Geom;                line1 = (LineString) var2.getGeometryN(0);            } else if (gx1Geom instanceof LineString) {                line1 = (LineString) gx1Geom;            }            Geometry gx2Geom = reader.read(crossSectionAnalysisVo.getWkt());            LineString line2 = null;            if (gx2Geom instanceof MultiLineString) {                MultiLineString var2 = (MultiLineString) gx2Geom;                line2 = (LineString) var2.getGeometryN(0);            } else if (gx2Geom instanceof LineString) {                line2 = (LineString) gx2Geom;            }            this.calIntersectionData(spatialDtoWKT, crossSectionAnalysisVo);            calculationVerticalDistance(line1, line2, crossSectionAnalysisVo);        }        return sectionAnalysisVoList;    }    /**     * 计算交点，及交点埋深、高程     *     * @param wkt     * @param vo     * @throws ParseException     */    private void calIntersectionData(String wkt, CrossSectionAnalysisVo vo) throws ParseException {        GeometryFactory geometryFactory = JTSFactoryFinder.getGeometryFactory(null);        //雨水污水以管底计算,其他按管顶计算        boolean flag = vo.getYsdm().startsWith("401") || vo.getYsdm().startsWith("402");        String qdms = vo.getQdms();        String zdms = vo.getZdms();        BigDecimal qdgc = vo.getQdgc();        BigDecimal zdgc = vo.getZdgc();        String gj = vo.getGj();        double gjDouble = Double.parseDouble(gj);        BigDecimal gjDecimal = BigDecimal.valueOf(gjDouble);        String gxShapeText = vo.getCoordinate();        WKTReader reader = new WKTReader(geometryFactory);        Geometry lrrlGeom = reader.read(wkt);        Geometry gxGeom = reader.read(gxShapeText);        LineString lrrlLineString = (LineString) lrrlGeom;        LineString gxLineString = null;        if (gxGeom instanceof MultiLineString) {            MultiLineString var2 = (MultiLineString) gxGeom;            gxLineString = (LineString) var2.getGeometryN(0);        } else if (gxGeom instanceof LineString) {            gxLineString = (LineString) gxGeom;        }        Geometry intersectionPoint = lrrlLineString.intersection(gxLineString);        Point startPoint = gxLineString.getStartPoint();        Point endPoint = gxLineString.getEndPoint();        double length = gxLineString.getLength();        double distance = startPoint.distance(intersectionPoint);        vo.setIntersectionPoint(intersectionPoint.toString());        if (!com.njcky.utils.page.StringUtils.isEmpty(qdms) && !com.njcky.utils.page.StringUtils.isEmpty(zdms)) {            BigDecimal qdmsDouble = BigDecimal.valueOf(Double.parseDouble(qdms));            BigDecimal zdmsDouble = BigDecimal.valueOf(Double.parseDouble(zdms));            if (flag) {                qdmsDouble = qdmsDouble.subtract(gjDecimal.divide(BigDecimal.valueOf(1000), MathContext.DECIMAL64));                zdmsDouble = zdmsDouble.subtract(gjDecimal.divide(BigDecimal.valueOf(1000), MathContext.DECIMAL64));            }            BigDecimal var1 = zdmsDouble.subtract(qdmsDouble);            BigDecimal var2 = BigDecimal.valueOf(distance).divide(BigDecimal.valueOf(length), MathContext.DECIMAL64).min(var1);            BigDecimal var3 = qdmsDouble.add(var2);            vo.setIntersectionMs(var3.doubleValue());        }        if (qdgc != null && zdgc != null) {            if (!flag) {                qdgc = qdgc.subtract(gjDecimal.divide(BigDecimal.valueOf(1000), MathContext.DECIMAL64));                zdgc = zdgc.subtract(gjDecimal.divide(BigDecimal.valueOf(1000), MathContext.DECIMAL64));            }            //高程差            BigDecimal var4 = zdgc.subtract(qdgc);            //计算比例            BigDecimal var5 = BigDecimal.valueOf(distance).divide(BigDecimal.valueOf(length), MathContext.DECIMAL64).min(var4);            //交点高程            BigDecimal var6 = qdgc.add(var5);            //保存交点高程            vo.setIntersectionGc(var6.doubleValue());        }    }    private CrossSectionAnalysisVo calculationVerticalDistance(LineString line1, LineString line2, CrossSectionAnalysisVo crossSection) {        Geometry interPoint = line1.intersection(line2);// 相交点        //相交点到管线的距离,算出的是度        double line1Distance = line1.getStartPoint().distance(interPoint);        //相交点到线2的距离        BigDecimal line2Distance = BigDecimal.valueOf(line2.getStartPoint().distance(interPoint));        //线2的长度        BigDecimal line2Length = BigDecimal.valueOf(line2.getLength());        //占比        BigDecimal ratio2 = line2Distance.divide(line2Length, RoundingMode.HALF_UP);        // 计算相交管线的管点高程、覆土深度，v1abs是高程差；        if (crossSection.getQdgc() != null && crossSection.getZdgc() != null) {            //起点高程-终点高程            BigDecimal gcAbs = (crossSection.getQdgc().subtract(crossSection.getZdgc()));            BigDecimal gc = crossSection.getZdgc()                    .add((gcAbs.multiply(ratio2)));            crossSection.setPointGc(gc);        }        if (crossSection.getGxqdfthd() != null && crossSection.getGxzdfthd() != null) {            BigDecimal ftAbs = (crossSection.getGxqdfthd().subtract(crossSection.getGxzdfthd()));            BigDecimal ms = crossSection.getGxzdfthd()                    .add((ftAbs.multiply(ratio2)));            crossSection.setPointMs(ms);        } else {            BigDecimal ftAbs = (BigDecimal.valueOf(Double.valueOf(crossSection.getQdms())).subtract(BigDecimal.valueOf(Double.valueOf(crossSection.getZdms()))));            BigDecimal ms = BigDecimal.valueOf(Double.valueOf(crossSection.getQdms()))                    .add((ftAbs.multiply(ratio2)));            crossSection.setPointMs(ms);        }        //获取划线起点与相交管线的距离,转成米        BigDecimal distance = BigDecimal.valueOf(0);        if (interPoint.getSRID() != 0) {            distance = BigDecimal.valueOf(line1Distance).multiply(BigDecimal.valueOf(VAL));        } else {            distance = BigDecimal.valueOf(line1Distance);        }        crossSection.setDistance(distance);        return crossSection;    }    //计算两点距离    public Object calcDistance(String spatialDtoWKT) {        String points[] = spatialDtoWKT.split(",");        int index = points[0].indexOf("(");        String point1 = points[0].substring(index + 1);        String point2 = points[1].replace(")", "");        String sql = "SELECT sde.ST_Distance(sde.st_transform(sde.st_geometry('POINT(" + point1 + ")',4490), 4528),sde.st_transform(sde.st_geometry('POINT(" + point2 + ")',4490), 4528))";        Object result = poolDataSourceUtil.NpgNormal_GetScalar(sql).toString();        return result;    }}